#！/bin/bash

source /etc/profile

# 可以输入日期；如果未输入日期取昨天的时间
if [ -n "$1" ]
then
	do_date=$1
else
	do_date=`date -d "-1 day" +%F`
fi

# 定义要执行的SQL
sql="
--7前天登录了，最近7天没登录
with tmp as(
select 
a.device_id as id1,
b.device_id as id2
from
(
--七天前的数据
select device_id
from dws.dws_member_start_day
where dt=date_add('$do_date', -7)) a
left join
(
--最近七天的数据
select device_id
from dws.dws_member_start_day
where dt > date_add('$do_date', -7) and dt <= '$do_date'
group by device_id
) b
on a.device_id = b.device_id)
insert overwrite table dws.dws_member_silent_day
partition(dt='$do_date')
select id1 from tmp 
where  id2 is null
"

hive -e "$sql"
